package com.gaozhaoyang.demo;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBC_Demo03 {
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    @Test
    public void addUser() throws SQLException {
        //获取可执行对象
        String sql = "insert into user values(?,?,?)";
        ps = con.prepareStatement(sql);
        //给占位符赋值
        ps.setInt(1,3);
        ps.setString(2,"gzy");
        ps.setString(3,"754165");
        //自行并获取结果
        System.out.println(ps.executeUpdate() > 0 ? "添加成功" : "添加失败");
    }
    @Test
    public void updateUser() throws SQLException {
        //获取可执行对象
        String sql = "update user set password =? where uid =?";
        ps = con.prepareStatement(sql);
        //给占位符赋值
        ps.setString(1,"555");
        ps.setInt(2,1);
        //执行并获取结果
        System.out.println(ps.executeUpdate() > 0 ? "修改成功" : "修改失败");
    }

    @Test
    public void deleteUser() throws SQLException {
        //获取可执行
        String sql = "delete from user where uid =?";
        ps = con.prepareStatement(sql);
        //给?占位符设置参数
        ps.setInt(1,1);
        //执行并获取结果
        System.out.println(ps.executeUpdate() > 0 ? "删除成功" : "删除失败");
    }

    @Test
    public void findUser() throws SQLException {
        //获取可执行对象
        String sql = "select * from user";
        ps= con.prepareStatement(sql);
        //执行并获取结果
        rs = ps.executeQuery();

        while (rs.next()) {
            int uid = rs.getInt(1);
            String username = rs.getString(2);
            String password = rs.getString(3);
            System.out.println(uid + "\t" + username + "\t" + password);
        }

    }

    @Before
    public void getConnection1() throws SQLException {
        con=dataSource.getConnection();
    }


    @After
    public void closeResrouce() throws SQLException {

        if (rs!=null) {
            rs.close();
        }


        if (ps!=null) {
            ps.close();
        }

        if (con!=null) {
            con.close();
        }
    }
}
